set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;


with dwd_yl_oms_oms_waybill_incre_dt as (
select
     t.waybill_no
    ,t.sign_network_code   as sign_network_code  --签收网点
    ,tt3.name as sign_network_name  --签收网点
    ,tt3.agent_code    as sign_agent_code                  --'代理区编码',
    ,tt3.agent_name    as sign_agent_name                 --'代理区名称,',
    ,tt3.provider_id   as sign_provider_id                --'省份ID',
    ,tt3.provider_desc as sign_provider_desc            --'省份',
    ,tt3.city_id       as sign_city_id                   --'城市ID',
    ,tt3.city_desc     as sign_city_desc                 --'城市',
    ,t.terminal_dispatch_code   --面单三段码
    ,t5.third_code            --驿站补推三段码
    ,t5.courier_station_code   --驿站码编码
    ,t.station_unique_number    --驿站码唯一标识
    ,case when t.order_source_code  ='D09'  then 1 else 0 end    as is_txd
    ,case when t.order_source_code  ='D801' then 2 else 0 end    as is_qxt
    ,case when t.order_source_code <> 'D09' and t.order_source_code <> 'D801' then 1 else 0 end  is_jms
    ,t5.input_type  --'补推来源'
    ,t5.input_type_name  --'补推来源'
    ,to_date(t.sign_time) as dt
from  jms_dwd.dwd_yl_oms_oms_waybill_incre_dt t
left join jms_dim.dim_network_whole_massage  tt3 on t.sign_network_code=tt3.code
left join jms_dim.dim_yl_lmdm_agent_point t5 on  t.station_unique_number=t5.unique_identify
where t.dt between date_add('{{ execution_date  | cst_ds }}', -15) and '{{ execution_date  | cst_ds }}'
and to_date(sign_time)='{{ execution_date  | cst_ds }}'
and t.is_sign='1'
),
dwd_tab_barscan_collect_base_dt as (
    select t.waybill_no,t4.tail_code from (
        select
         t.waybill_no
        ,t.send_deliver_usercode
        ,row_number() over(partition by t.waybill_no order by t.scan_time desc ) as rn
        from  jms_dwd.dwd_tab_barscan_collect_base_dt t
        where t.dt between date_add('{{ execution_date  | cst_ds }}', -15) and '{{ execution_date  | cst_ds }}'
    ) t
    left join  (
                 select  t1.staff_code,
                         t1.tail_code
                 from jms_dim.dim_lmdm_sys_tail_code t
                          join jms_dim.dim_lmdm_sys_tail_code_staff t1 on t.id=t1.tail_id
                 where   t.is_enable='1' and t.is_delete='1'
                 group by  t1.staff_code, t1.tail_code
             ) t4 on t.send_deliver_usercode=t4.staff_code
    where t.rn =1
),
dwd_tab_barscan_deliver_base_dt as (
 select
   t.waybill_no
from jms_dwd.dwd_tab_barscan_deliver_base_dt t
where t.dt between date_add('{{ execution_date  | cst_ds }}', -15) and '{{ execution_date  | cst_ds }}'
and ((t.pistol_id='DDGP20220907001'  and t.check_flag='14') or t.pistol_id='HTKY')
group by t.waybill_no
)

insert overwrite table jms_dm.dm_lmdm_four_segment_push_detail_dt partition (dt)
select
 t.waybill_no
,t.sign_network_code    --签收网点
,t.sign_network_name    --签收网点
,t.sign_agent_code      --'代理区编码',
,t.sign_agent_name      --'代理区名称,',
,t.sign_provider_id      --'省份ID',
,t.sign_provider_desc    --'省份',
,t.sign_city_id          --'城市ID',
,t.sign_city_desc        --'城市',
,t.terminal_dispatch_code --'面单三短码',
,1 as is_deliver          --是否出仓
,t3.tail_code as deliver_third_code --出仓业务员三段码
,t.third_code            --驿站补推三段码
,t.courier_station_code   --驿站码编码
,t.station_unique_number    --驿站码唯一标识
,t.is_txd  --是否推送桃花岛
,t.is_qxt  --是否推送七星潭
,t.is_jms --是否推JMS
,t.input_type  --'补推来源'
,t.input_type_name  --'补推来源'
,t.dt as date_time  --查询日期
,t.dt
from  dwd_yl_oms_oms_waybill_incre_dt t
left join dwd_tab_barscan_deliver_base_dt  t2 on t2.waybill_no=t.waybill_no
left join dwd_tab_barscan_collect_base_dt  t3 on t.waybill_no=t3.waybill_no
where t2.waybill_no is not null
 distribute by pmod(hash(rand()), 30)

;
